Skip Headers

Oracle® Data Provider for .NET Developer's Guide 10g Release 2 (10.2) Part Number B14307-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OracleCommandBuilder Class

An `OracleCommandBuilder` object provides automatic SQL generation for the `OracleDataAdapter` when updates are made to the database.

Class Inheritance

`Object`

`MarshalByRefObject`

`Component`

`OracleCommandBuilder`

Declaration

    // C#
    public sealed class OracleCommandBuilder : Component

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

`OracleCommandBuilder` automatically generates SQL statements for single-table updates when the `SelectCommand` property of the `OracleDataAdapter` is set. An exception is thrown if the `DataSet` contains multiple tables. The `OracleCommandBuilder` registers itself as a listener for `RowUpdating` events whenever its `DataAdapter` property is set. Only one `OracleDataAdapter` object and one `OracleCommandBuilder` object can be associated with each other at one time.

To generate `INSERT`, `UPDATE`, or `DELETE` statements, the `OracleCommandBuilder` uses `ExtendedProperties` within the `DataSet` to retrieve a required set of metadata. If the `SelectCommand` is changed after the metadata is retrieved (for example, after the first update), the `RefreshSchema` method should be called to update the metadata.

`OracleCommandBuilder` first looks for the metadata from the `ExtendedProperties` of the `DataSet`; if the metadata is not available, `OracleCommandBuilder` uses the `SelectCommand` property of the `OracleDataAdapter` to retrieve the metadata.

Example

The following example performs an update on the `EMP` table. It uses the `OracleCommandBuilder` object to create the `UpdateCommand` for the `OracleDataAdapter` object when `OracleDataAdapter.Update()` is called.

    // C#

    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class OracleCommandBuilderSample
    {
      static void Main()
      {
        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        string cmdstr = "SELECT empno, sal from emp";

        // Create the adapter with the selectCommand txt and the
        // connection string
        OracleDataAdapter adapter = new OracleDataAdapter(cmdstr, constr);

        // Create the builder for the adapter to automatically generate
        // the Command when needed
        OracleCommandBuilder builder = new OracleCommandBuilder(adapter);

        // Create and fill the DataSet using the EMP
        DataSet dataset = new DataSet();
        adapter.Fill(dataset, "EMP");

        // Get the EMP table from the dataset
        DataTable table = dataset.Tables["EMP"];

        // Indicate DataColumn EMPNO is unique
        // This is required by the OracleCommandBuilder to update the EMP table
        table.Columns["EMPNO"].Unique = true;

        // Get the first row from the EMP table
        DataRow row = table.Rows[0];

        // Update the salary
        double sal = double.Parse(row["SAL"].ToString());
        row["SAL"] = sal + .01;

        // Now update the EMP using the adapter
        // The OracleCommandBuilder will create the UpdateCommand for the
        // adapter to update the EMP table
        adapter.Update(dataset, "EMP");

        Console.WriteLine("Row updated successfully");
      }
    }

Requirements

Namespace: `Oracle.DataAccess.Client`

Assembly: `Oracle.DataAccess.dll`

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Members OracleCommandBuilder Constructors OracleCommandBuilder Static Methods OracleCommandBuilder Properties OracleCommandBuilder Public Methods OracleCommandBuilder Events

## OracleCommandBuilder Members

`OracleCommandBuilder` members are listed in the following tables:

OracleCommandBuilder Constructors

`OracleCommandBuilder` constructors are listed in Table 5-8.

Table 5-8 OracleCommandBuilder Constructors

Constructor | Description
--- | ---
OracleCommandBuilder Constructors | Instantiates a new instance of `OracleCommandBuilder` class (Overloaded)

OracleCommandBuilder Static Methods

`OracleCommandBuilder` static methods are listed in Table 5-9.

Table 5-9 OracleCommandBuilder Static Methods

Methods | Description
--- | ---
DeriveParameters | Queries for the parameters of a stored procedure or function, represented by a specified `OracleCommand`, and populates the `OracleParameterCollection` of the command with the return values
`Equals` | Inherited from `Object` (Overloaded)

OracleCommandBuilder Properties

`OracleCommandBuilder` properties are listed in Table 5-10.

Table 5-10 OracleCommandBuilder Properties

Name | Description
--- | ---
`Container` | Inherited from `Component`
DataAdapter | Indicates the `OracleDataAdapter` for which the SQL statements are generated
CaseSensitive | Indicates whether or not double quotes are used around Oracle object names when generating SQL statements
`Site` | Inherited from `Component`

OracleCommandBuilder Public Methods

`OracleCommandBuilder` public methods are listed in Table 5-11.

Table 5-11 OracleCommandBuilder Public Methods

Public Method | Description
--- | ---
`CreateObjRef` | Inherited from `MarshalByRefObject`
`Dispose` | Inherited from `Component`
`Equals` | Inherited from `Object` (Overloaded)
GetDeleteCommand | Gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform deletions on the database
`GetHashCode` | Inherited from `Object`
GetInsertCommand | Gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform insertions on the database
`GetLifetimeService` | Inherited from `MarshalByRefObject`
`GetType` | Inherited from `Object`
GetUpdateCommand | Gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform updates on the database
`InitializeLifetimeService` | Inherited from `MarshalByRefObject`
RefreshSchema | Refreshes the database schema information used to generate `INSERT`, `UPDATE`, or `DELETE` statements
`ToString` | Inherited from `Object`

OracleCommandBuilder Events

The `OracleCommandBuilder` event is listed in Table 5-12.

Table 5-12 OracleCommandBuilder Events

Event Name | Description
--- | ---
`Disposed` | Inherited from `Component`

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class

## OracleCommandBuilder Constructors

`OracleCommandBuilder` constructors create new instances of the `OracleCommandBuilder` class.

Overload List:

- OracleCommandBuilder()

  This constructor creates an instance of the `OracleCommandBuilder` class.

- OracleCommandBuilder(OracleDataAdapter)

  This constructor creates an instance of the `OracleCommandBuilder` class and sets the `DataAdapter` property to the provided `OracleDataAdapter` object.

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### OracleCommandBuilder()

This constructor creates an instance of the `OracleCommandBuilder` class.

Declaration

    // C#
    public OracleCommandBuilder();

Remarks

Default constructor.

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### OracleCommandBuilder(OracleDataAdapter)

This constructor creates an instance of the `OracleCommandBuilder` class and sets the `DataAdapter` property to the provided `OracleDataAdapter` object.

Declaration

    // C#
    public OracleCommandBuilder(OracleDataAdapter da);

Parameters

- `da`

  The `OracleDataAdapter` object provided.

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

## OracleCommandBuilder Static Methods

`OracleCommandBuilder` static methods are listed in Table 5-13.

Table 5-13 OracleCommandBuilder Static Methods

Methods | Description
--- | ---
DeriveParameters | Queries for the parameters of a stored procedure or function, represented by a specified `OracleCommand`, and populates the `OracleParameterCollection` of the command with the return values
`Equals` | Inherited from `Object` (Overloaded)

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### DeriveParameters

This method queries for the parameters of a stored procedure or function, represented by a specified `OracleCommand`, and populates the `OracleParameterCollection` of the command with the return values.

Declaration

    // C#
    public static void DeriveParameters(OracleCommand command);

Parameters

- `command`

  The command that represents the stored procedure or function for which parameters are to be derived.

Exceptions

`InvalidOperationException` - The `CommandText` is not a valid stored procedure or function name, the `CommandType` is not `CommandType.StoredProcedure`, or the `Connection.State` is not `ConnectionState.Open`.

Remarks

When `DeriveParameters` is used to populate the `Parameter` collection of an `OracleCommand` Object that represents a stored function, the return value of the function is bound as the first parameter (at position `0` of the `OracleParameterCollection`).

`DeriveParameters` can only be used for stored procedures or functions, not for anonymous PL/SQL blocks.

Invoking `DeriveParameters` deletes all existing parameters in the parameter collection of the command.

`DeriveParameters` incurs a database round-trip and should only be used during design time. To avoid unnecessary database round-trips in a production environment, the `DeriveParameters` method itself should be replaced with the explicit parameter settings that were returned by the `DeriveParameters` method at design time.

`DeriveParameters` can only preserve the case of the stored procedure or function name if it is encapsulated by double-quotes. For example, if the stored procedure in the database is named `GetEmployees` with mixed-case, the `CommandText` property on the `OracleCommand` object must be set appropriately as in the following example:

    cmd.CommandText = "\"GetEmployees\"";

Stored procedures and functions in a package must be provided in the following format:

    <package name>.<procedure or function name>

For example, to obtain parameters for a stored procedure named `GetEmployees` (mixed-case) in a package named `EmpProcedures` (mixed-case), the name provided to the `OracleCommand` is:

    "\"EmpProcedures\".\"GetEmployees\""

`DeriveParameters` cannot be used for object type methods.

The derived parameters contain all the metadata information that is needed for the stored procedure to execute properly. The application must provide the value of the parameters before execution, if required. The application may also modify the metadata information of the parameters before execution. For example, the `Size` property of the `OracleParameter` may be modified for PL/SQL character and string types to optimize the execution of the stored procedure.

The output values of derived parameters return as .NET Types by default. To obtain output parameters as provider types, the `OracleDbType` property of the parameter must be set explicitly by the application to override this default behavior. One quick way to do this is to set the `OracleDbType` to itself for all output parameters that should be returned as provider types.

The `BindByName` property of the supplied `OracleCommand` is left as is, but the application can change its value.

If the specified stored procedure or function is overloaded, the first overload is used to populate the parameters collection.

    // Database Setup
    /*
    connect scott/tiger@oracle
    CREATE OR REPLACE PROCEDURE MyOracleStoredProc (arg_in IN VARCHAR2,
      arg_out OUT VARCHAR2) IS
    BEGIN
      arg_out := arg_in;
    END;
    /
    */

    // C#
    using System;
    using System.Data;
    using Oracle.DataAccess.Client;

    class DeriveParametersSample
    {
      static void Main()
      {
        // Create the PL/SQL Stored Procedure MyOracleStoredProc as indicated in
        // the preceding Database Setup

        string constr = "User Id=scott;Password=tiger;Data Source=oracle";
        OracleConnection con = new OracleConnection(constr);
        con.Open();

        // Create an OracleCommand
        OracleCommand cmd = new OracleCommand("MyOracleStoredProc", con);
        cmd.CommandType = CommandType.StoredProcedure;

        // Derive Parameters
        OracleCommandBuilder.DeriveParameters(cmd);
        Console.WriteLine("Parameters Derived");

        // Prints "Number of Parameters for MyOracleStoredProc = 2"
        Console.WriteLine("Number of Parameters for MyOracleStoredProc = {0}",
          cmd.Parameters.Count);

        // The PL/SQL stored procedure MyOracleStoredProc has one IN and
        // one OUT parameter.  Set the Value for the IN parameter.
        cmd.Parameters[0].Value = "MyText";

        // The application may modify the other OracleParameter properties also
        // This sample uses the default Size for the IN parameter and modifies
        // the Size for the OUT parameter

        // The default size for OUT VARCHAR2 is 4000
        // Prints "cmd.Parameters[1].Size  = 4000"
        Console.WriteLine("cmd.Parameters[1].Size  = " + cmd.Parameters[1].Size);

        // Set the Size for the OUT parameter
        cmd.Parameters[1].Size = 6;

        // Execute the command
        cmd.ExecuteNonQuery();

        // Prints "cmd.Parameters[1].Value = MyText"
        Console.WriteLine("cmd.Parameters[1].Value = " + cmd.Parameters[1].Value);

        con.Close();
        con.Dispose();
      }
    }

Example

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members OracleCommand Class OracleParameter Class OracleParameterCollection Class `http://msdn.microsoft.com/library` for detailed information about this Microsoft .NET Framework 1.1 feature

## OracleCommandBuilder Properties

`OracleCommandBuilder` properties are listed in Table 5-14.

Table 5-14 OracleCommandBuilder Properties

Name | Description
--- | ---
`Container` | Inherited from `Component`
DataAdapter | Indicates the `OracleDataAdapter` for which the SQL statements are generated
CaseSensitive | Indicates whether or not double quotes are used around Oracle object names when generating SQL statements
`Site` | Inherited from `Component`

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### DataAdapter

This property indicates the `OracleDataAdapter` for which the SQL statements are generated.

Declaration

    // C#
    OracleDataAdapter DataAdapter{get; set;}

Property Value

`OracleDataAdapter`

Remarks

Default = `null`

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### CaseSensitive

This property indicates whether or not double quotes are used around Oracle object names (for example, tables or columns) when generating SQL statements.

Declaration

    // C#
    bool CaseSensitive {get; set;}

Property Value

A `bool` that indicates whether or not double quotes are used.

Remarks

Default = `false`

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

## OracleCommandBuilder Public Methods

`OracleCommandBuilder` public methods are listed in Table 5-15.

Table 5-15 OracleCommandBuilder Public Methods

Public Method | Description
--- | ---
`CreateObjRef` | Inherited from `MarshalByRefObject`
`Dispose` | Inherited from `Component`
`Equals` | Inherited from `Object` (Overloaded)
GetDeleteCommand | Gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform deletions on the database
`GetHashCode` | Inherited from `Object`
GetInsertCommand | Gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform insertions on the database
`GetLifetimeService` | Inherited from `MarshalByRefObject`
`GetType` | Inherited from `Object`
GetUpdateCommand | Gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform updates on the database
`InitializeLifetimeService` | Inherited from `MarshalByRefObject`
RefreshSchema | Refreshes the database schema information used to generate `INSERT`, `UPDATE`, or `DELETE` statements
`ToString` | Inherited from `Object`

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### GetDeleteCommand

This method gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform deletions on the database when an application calls `Update()` on the `OracleDataAdapter`.

Declaration

    // C#
    public OracleCommand GetDeleteCommand();

Return Value

An `OracleCommand`.

Exceptions

`ObjectDisposedException` - The `OracleCommandBuilder` object is already disposed.

`InvalidOperationException` - Either the `SelectCommand` or the `DataAdapter` property is null, or the primary key cannot be retrieved from the `SelectCommand` property of the `OracleDataAdapter`.

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### GetInsertCommand

This method gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform insertions on the database when an application calls `Update()` on the `OracleDataAdapter`.

Declaration

    // C#
    public OracleCommand GetInsertCommand();

Return Value

An `OracleCommand`.

Exceptions

`ObjectDisposedException` - The `OracleCommandBuilder` object is already disposed.

`InvalidOperationException` - Either the `SelectCommand` or the `DataAdapter` property is null, or the primary key cannot be retrieved from the `SelectCommand` property of the `OracleDataAdapter`.

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### GetUpdateCommand

This method gets the automatically generated `OracleCommand` object that has the SQL statement (`CommandText`) perform updates on the database when an application calls `Update()` on the `OracleDataAdapter`.

Declaration

    // C#
    public OracleCommand GetUpdateCommand();

Return Value

An `OracleCommand`.

Exceptions

`ObjectDisposedException` - The `OracleCommandBuilder` object is already disposed.

`InvalidOperationException` - Either the `SelectCommand` or the `DataAdapter` property is null, or the primary key cannot be retrieved from the `SelectCommand` property of the `OracleDataAdapter`.

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

### RefreshSchema

This method refreshes the database schema information used to generate `INSERT`, `UPDATE`, or `DELETE` statements.

Declaration

    // C#
    public void RefreshSchema();

Remarks

An application should call `RefreshSchema` whenever the `SelectCommand` value of the `OracleDataAdapter` changes.

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members

## OracleCommandBuilder Events

The `OracleCommandBuilder` event is listed in Table 5-16.

Table 5-16 OracleCommandBuilder Event

Event Name | Description
--- | ---
`Disposed` | Inherited from `Component`

See Also: "Oracle.DataAccess.Client Namespace" OracleCommandBuilder Class OracleCommandBuilder Members